import * as vscode from 'vscode';
import * as fs from 'fs';
import * as path from 'path';
import * as FileUtils from './utils/FileUtils';
import * as ExcelUtils from './utils/ExcelUtils';
import * as SQLUtils from './utils/SQLUtils';

export async function genTemplate() {
  // 获取当前工作区
  const workspaceFolders = vscode.workspace.workspaceFolders;
  if (!workspaceFolders) {
    vscode.window.showErrorMessage('请先打开一个工作区');
    return;
  }

  const editor = vscode.window.activeTextEditor;
  if (!editor) {
    vscode.window.showErrorMessage('No active editor found!');
    return;
  }

  if (editor.document.languageId !== 'markdown') {
    vscode.window.showErrorMessage('当前文档不是Markdown文件');
    return;
  }

  const position = editor.selection.active;
  const currentLineNumber = position.line;

  const lineContent = editor.document.lineAt(currentLineNumber).text;
  if (!lineContent.startsWith("#### ")) {
    vscode.window.showErrorMessage('当前行非####');
    return;
  }

  const kpiName = lineContent.substring(5).trim();


  const relativePath = './kpi/kpi.json';
  const fullPath = path.join(workspaceFolders[0].uri.fsPath, relativePath);
  if (!fs.statSync(fullPath).isFile()) {
    vscode.window.showErrorMessage(`${relativePath} is not found!`);
    return;
  }

  const kpiJson = FileUtils.readJsonFile(fullPath);
  if (!Array.isArray(kpiJson)) {
    vscode.window.showErrorMessage(`${relativePath} format error!`);
    return;
  }

  const filterdKpis = kpiJson.filter(json => json["指标名称辅助列"] === kpiName);
  if (filterdKpis.length > 1) {
    vscode.window.showErrorMessage(`${kpiName} is duplicate in json!`);
    return;
  }
  if (filterdKpis.length < 1) {
    vscode.window.showErrorMessage(`${kpiName} is not found in json!`);
    return;
  }
  const kpi = filterdKpis[0];
  const define_formula = kpi["*指标公式"];
  const kpi_desc = kpi["取数公式"];
  const template = [
    `- 指标公式: ${define_formula}`, 
    `- 取数公式: ${kpi_desc}`, 
    "", 
    "", 
    "```sql", 
    "", 
    "```", 
    "",
    ""
  ].join("\n");

  editor.edit(editBuilder => {
    editBuilder.insert(new vscode.Position(currentLineNumber+1, 0), template);
  }).then(success => {
    if (!success) {
      vscode.window.showErrorMessage(`插入模板失败`);
    } else {
      vscode.window.showInformationMessage(`插入模板成功！`);
    }
  });
}


// 创建自定义的诊断集合
const diagnosticCollection = vscode.languages.createDiagnosticCollection('vscode-text-util');

export async function checkDuplicate() {
  // 获取当前工作区
  const workspaceFolders = vscode.workspace.workspaceFolders;
  if (!workspaceFolders) {
    vscode.window.showErrorMessage('请先打开一个工作区');
    return;
  }

  const editor = vscode.window.activeTextEditor;
  if (!editor) {
    vscode.window.showErrorMessage('No active editor found!');
    return;
  }

  if (editor.document.languageId !== 'markdown') {
    vscode.window.showErrorMessage('当前文档不是Markdown文件');
    return;
  }

  const kpiMap = new Map<string, number>();
  const duplicateMap = new Map<string, number>();
  const lineCount = editor.document.lineCount;
  for (let lineNumber = 0; lineNumber < lineCount; lineNumber++) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    if (lineContent.startsWith("#### ")) {
      const kpiName = lineContent.substring(5).trim();
      if (kpiMap.has(kpiName)) {
        duplicateMap.set(kpiName, lineNumber);
      } else {
        kpiMap.set(kpiName, lineNumber);
      }
    }
  }


  const uri = editor.document.uri;
  const diagnostics: vscode.Diagnostic[] = [];
  if (duplicateMap.size > 0) {
    for (const [kpiName, lineNumber] of duplicateMap) {
      const lineContent = editor.document.lineAt(lineNumber).text;
      const diagnostic: vscode.Diagnostic = {
        range: new vscode.Range(lineNumber, 0, lineNumber, lineContent.length),
        message: `重复的kpi:${kpiName}`,
        severity: vscode.DiagnosticSeverity.Error,
        source: 'vscode-text-util'
      }
      diagnostics.push(diagnostic);
    }
  }
  vscode.window.showInformationMessage('检查完成');
  diagnosticCollection.set(uri, diagnostics);
}


export async function addOtherDims() {

  // 获取当前工作区
  const workspaceFolders = vscode.workspace.workspaceFolders;
  if (!workspaceFolders) {
    vscode.window.showErrorMessage('请先打开一个工作区');
    return;
  }

  const editor = vscode.window.activeTextEditor;
  if (!editor) {
    vscode.window.showErrorMessage('No active editor found!');
    return;
  }

  const relativePath = './metadata/tables';
  const fullPath = path.join(workspaceFolders[0].uri.fsPath, relativePath);
  const isDirectory = fs.statSync(fullPath).isDirectory();
  if (!isDirectory) {
    vscode.window.showErrorMessage('./metadata/tables dir not found!');
    return;
  }

  const jsonFileResults = await FileUtils.readAllJsonFiles(fullPath);
  const tablesColumns = jsonFileResults.map(result => {
    return result.content;
  });


  if (editor.document.languageId !== 'markdown') {
    vscode.window.showErrorMessage('当前文档不是Markdown文件');
    return;
  }

  const lineCount = editor.document.lineCount;
  const position = editor.selection.active;
  const currentLineNumber = position.line;

  let startLine = 0;
  let endLine = lineCount;
  const lines: { lineNumber: number, lineContent: string }[] = [];
  for (let lineNumber = currentLineNumber; lineNumber >= 0; lineNumber--) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    lines.push({ lineNumber, lineContent });
    if (lineContent.startsWith("#### ")) {
      startLine = lineNumber;
      break;
    }
  }
  for (let lineNumber = currentLineNumber + 1; lineNumber < lineCount; lineNumber++) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    if (lineContent.startsWith("#### ") || lineContent.startsWith("### ") || lineContent.startsWith("## ") || lineContent.startsWith("# ")) {
      endLine = lineNumber - 1;
      break;
    }
    lines.push({ lineNumber, lineContent });
  }

  lines.sort((a, b) => a.lineNumber - b.lineNumber);

  let dims: { dimName: string, dimCode: string }[] = [];
  let mDims: { dimName: string, dimCode: string }[] = [];
  let otherDimsLine: number = 0;
  let sqlLine: number = 0;
  const dimRegex = /(.*?)\((.*?)\)+/;
  lines.forEach(line => {
    if (line.lineContent.startsWith("- 维度:")) {
      const dimsStr = line.lineContent.substring(6).trim().split(" ");
      dimsStr.forEach(dimStr => {
        dimStr = dimStr.trim();
        const match = dimStr.match(dimRegex);
        if (match) {
          const dimName = match[1];
          const dimCode = match[2];
          dims.push({ dimName, dimCode });
        }
      });
    } else if (line.lineContent.startsWith("- 度量:")) {
      const mDimsStr = line.lineContent.substring(6).trim().split(" ");
      mDimsStr.forEach(dimStr => {
        dimStr = dimStr.trim();
        const match = dimStr.match(dimRegex);
        if (match) {
          const dimName = match[1];
          const dimCode = match[2];
          mDims.push({ dimName, dimCode });
        }
      });
    } else if (line.lineContent.startsWith("- 其他维度:")) {
      otherDimsLine = line.lineNumber;
    } else if (line.lineContent.startsWith("```sql")) {
      sqlLine = line.lineNumber + 1;
    }
  });


  if (!mDims) {
    vscode.window.showErrorMessage('无度量维');
    return;
  }

  let tableName = "";
  let tableColumns = [];
  for (let tcs of tablesColumns) {
    if (!Array.isArray(tcs)) {
      continue;
    }
    const filterd = tcs.filter(tc => tc.columnName === mDims[0].dimCode);
    if (filterd && filterd.length > 0) {
      tableName = filterd[0].tableName;
      tableColumns = tcs;
      break;
    }
  }

  if (!tableName) {
    vscode.window.showErrorMessage('找不到匹配的表');
    return;
  }

  let otherDims: string[] = [];
  if (tableName === "SINOPEC.ZBYY.CWYY.004_QMYS.H/F7AS_ZB_H_CW0003") {

  } else if (tableName === "SINOPEC.ZBYY.CWYY.004_QMYS.H/F7AS_ZB_H_CW0004") {
    otherDims.push("报表编码(GHBBZDBH)");
    otherDims.push("列坐标(KHLZDZB)");
    otherDims.push("行坐标(KHHZDZB)");
    otherDims.push("上市判定(G0SSPD)");
  } else {
    otherDims.push("报表字段编号(BBZD_BH)");
    otherDims.push("列坐标(LZD_ORDE)");
    otherDims.push("行坐标(HZD_ORDE)");
    otherDims.push("汇总类别编号(LBZD_BH)");
  }

  if (otherDimsLine !== 0) {
    editor.edit(editBuilder => {
      editBuilder.insert(new vscode.Position(otherDimsLine, 8), otherDims.join(" "));
    }).then(success => {
      if (!success) {
        vscode.window.showErrorMessage('插入otherDims失败');
      } else {
        vscode.window.showInformationMessage('插入otherDims成功');
      }
    });
  } else {
    vscode.window.showErrorMessage('无需插入otherDims');
  }
}


export async function genSQL() {
  // 获取当前工作区
  const workspaceFolders = vscode.workspace.workspaceFolders;
  if (!workspaceFolders) {
    vscode.window.showErrorMessage('请先打开一个工作区');
    return;
  }

  const editor = vscode.window.activeTextEditor;
  if (!editor) {
    vscode.window.showErrorMessage('No active editor found!');
    return;
  }

  const relativePath = './metadata/tables';
  const fullPath = path.join(workspaceFolders[0].uri.fsPath, relativePath);
  const isDirectory = fs.statSync(fullPath).isDirectory();
  if (!isDirectory) {
    vscode.window.showErrorMessage('./metadata/tables dir not found!');
    return;
  }

  const jsonFileResults = await FileUtils.readAllJsonFiles(fullPath);
  const tablesColumns = jsonFileResults.map(result => {
    return result.content;
  });


  if (editor.document.languageId !== 'markdown') {
    vscode.window.showErrorMessage('当前文档不是Markdown文件');
    return;
  }

  const lineCount = editor.document.lineCount;
  const position = editor.selection.active;
  const currentLineNumber = position.line;

  let startLine = 0;
  let endLine = lineCount;
  const lines: { lineNumber: number, lineContent: string }[] = [];
  for (let lineNumber = currentLineNumber; lineNumber >= 0; lineNumber--) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    lines.push({ lineNumber, lineContent });
    if (lineContent.startsWith("#### ")) {
      startLine = lineNumber;
      break;
    }
  }
  for (let lineNumber = currentLineNumber + 1; lineNumber < lineCount; lineNumber++) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    if (lineContent.startsWith("#### ") || lineContent.startsWith("### ") || lineContent.startsWith("## ") || lineContent.startsWith("# ")) {
      endLine = lineNumber - 1;
      break;
    }
    lines.push({ lineNumber, lineContent });
  }

  lines.sort((a, b) => a.lineNumber - b.lineNumber);

  let dims: { dimName: string, dimCode: string }[] = [];
  let mDims: { dimName: string, dimCode: string }[] = [];
  let otherDimsLine: number = 0;
  let sqlLine: number = 0;
  const dimRegex = /(.*?)\((.*?)\)+/;
  lines.forEach(line => {
    if (line.lineContent.startsWith("- 维度:")) {
      const dimsStr = line.lineContent.substring(6).trim().split(" ");
      dimsStr.forEach(dimStr => {
        dimStr = dimStr.trim();
        const match = dimStr.match(dimRegex);
        if (match) {
          const dimName = match[1];
          const dimCode = match[2];
          dims.push({ dimName, dimCode });
        }
      });
    } else if (line.lineContent.startsWith("- 度量:")) {
      const mDimsStr = line.lineContent.substring(6).trim().split(" ");
      mDimsStr.forEach(dimStr => {
        dimStr = dimStr.trim();
        const match = dimStr.match(dimRegex);
        if (match) {
          const dimName = match[1];
          const dimCode = match[2];
          mDims.push({ dimName, dimCode });
        }
      });
    } else if (line.lineContent.startsWith("- 其他维度:")) {
      otherDimsLine = line.lineNumber;
    } else if (line.lineContent.startsWith("```sql")) {
      sqlLine = line.lineNumber + 1;
    }
  });


  if (!mDims) {
    vscode.window.showErrorMessage('无度量维');
    return;
  }

  let tableName = "";
  let tableColumns = [];
  for (let tcs of tablesColumns) {
    if (!Array.isArray(tcs)) {
      continue;
    }
    const filterd = tcs.filter(tc => tc.columnName === mDims[0].dimCode);
    if (filterd && filterd.length > 0) {
      tableName = filterd[0].tableName;
      tableColumns = tcs;
      break;
    }
  }

  if (!tableName) {
    vscode.window.showErrorMessage('找不到匹配的表');
    return;
  }


  if (sqlLine !== 0) {
    const sql = SQLUtils.doGenSQL(dims, mDims, tableName);
    editor.edit(editBuilder => {
      editBuilder.insert(new vscode.Position(sqlLine, 0), sql);
    }).then(success => {
      if (!success) {
        vscode.window.showErrorMessage(`插入SQL失败:${sqlLine}`);
      } else {
        vscode.window.showInformationMessage(`插入SQL成功！`);
      }
    });
  } else {
    vscode.window.showErrorMessage(`插入SQL失败:未找到sql代码块`);
  }
}


export async function descriptionToSqlCondition() {
  // 获取当前工作区
  const workspaceFolders = vscode.workspace.workspaceFolders;
  if (!workspaceFolders) {
    vscode.window.showErrorMessage('请先打开一个工作区');
    return;
  }

  const editor = vscode.window.activeTextEditor;
  if (!editor) {
    vscode.window.showErrorMessage('No active editor found!');
    return;
  }

  const relativePath = './metadata/tables';
  const fullPath = path.join(workspaceFolders[0].uri.fsPath, relativePath);
  const isDirectory = fs.statSync(fullPath).isDirectory();
  if (!isDirectory) {
    vscode.window.showErrorMessage('./metadata/tables dir not found!');
    return;
  }

  const jsonFileResults = await FileUtils.readAllJsonFiles(fullPath);
  const tablesColumns = jsonFileResults.map(result => {
    return result.content;
  });


  if (editor.document.languageId !== 'markdown') {
    vscode.window.showErrorMessage('当前文档不是Markdown文件');
    return;
  }

  const lineCount = editor.document.lineCount;
  const position = editor.selection.active;
  const currentLineNumber = position.line;

  let startLine = 0;
  let endLine = lineCount;
  const lines: { lineNumber: number, lineContent: string }[] = [];
  for (let lineNumber = currentLineNumber; lineNumber >= 0; lineNumber--) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    lines.push({ lineNumber, lineContent });
    if (lineContent.startsWith("#### ")) {
      startLine = lineNumber;
      break;
    }
  }
  for (let lineNumber = currentLineNumber + 1; lineNumber < lineCount; lineNumber++) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    if (lineContent.startsWith("#### ") || lineContent.startsWith("### ") || lineContent.startsWith("## ") || lineContent.startsWith("# ")) {
      endLine = lineNumber - 1;
      break;
    }
    lines.push({ lineNumber, lineContent });
  }

  lines.sort((a, b) => a.lineNumber - b.lineNumber);

  let dims: { dimName: string, dimCode: string }[] = [];
  let mDims: { dimName: string, dimCode: string }[] = [];
  let otherDimsLine: number = 0;
  let sqlLine: number = 0;
  const dimRegex = /(.*?)\((.*?)\)+/;
  lines.forEach(line => {
    if (line.lineContent.startsWith("- 维度:")) {
      const dimsStr = line.lineContent.substring(6).trim().split(" ");
      dimsStr.forEach(dimStr => {
        dimStr = dimStr.trim();
        const match = dimStr.match(dimRegex);
        if (match) {
          const dimName = match[1];
          const dimCode = match[2];
          dims.push({ dimName, dimCode });
        }
      });
    } else if (line.lineContent.startsWith("- 度量:")) {
      const mDimsStr = line.lineContent.substring(6).trim().split(" ");
      mDimsStr.forEach(dimStr => {
        dimStr = dimStr.trim();
        const match = dimStr.match(dimRegex);
        if (match) {
          const dimName = match[1];
          const dimCode = match[2];
          mDims.push({ dimName, dimCode });
        }
      });
    } else if (line.lineContent.startsWith("- 其他维度:")) {
      otherDimsLine = line.lineNumber;
    } else if (line.lineContent.startsWith("```sql")) {
      sqlLine = line.lineNumber + 1;
    }
  });


  if (!mDims) {
    vscode.window.showErrorMessage('无度量维');
    return;
  }

  let tableName = "";
  let tableColumns = [];
  for (let tcs of tablesColumns) {
    if (!Array.isArray(tcs)) {
      continue;
    }
    const filterd = tcs.filter(tc => tc.columnName === mDims[0].dimCode);
    if (filterd && filterd.length > 0) {
      tableName = filterd[0].tableName;
      tableColumns = tcs;
      break;
    }
  }

  if (!tableName) {
    vscode.window.showErrorMessage('找不到匹配的表');
    return;
  }

  let otherDims: string[] = [];
  if (tableName === "SINOPEC.ZBYY.CWYY.004_QMYS.H/F7AS_ZB_H_CW0003") {
    vscode.window.showErrorMessage('指标取数不支持单元格转换');
    return;
  } else if (tableName === "SINOPEC.ZBYY.CWYY.004_QMYS.H/F7AS_ZB_H_CW0004") {
    otherDims.push("报表编码(GHBBZDBH)");
    otherDims.push("列坐标(KHLZDZB)");
    otherDims.push("行坐标(KHHZDZB)");
    otherDims.push("上市判定(G0SSPD)");
    SQLUtils.descriptionToSqlCondition("GHBBZDBH", "KHLZDZB", "KHHZDZB");
  } else if (tableName === "SINOPEC.EDW.03_BASAL_LAYER.124_FIRMS00.CW/FIRMS00_Z_SINO_GFBB_PRO_RPT_LBSJ") {
    otherDims.push("报表字段编号(BBZD_BH)");
    otherDims.push("列坐标(LZD_ORDE)");
    otherDims.push("行坐标(HZD_ORDE)");
    otherDims.push("汇总类别编号(LBZD_BH)");
    SQLUtils.descriptionToSqlCondition("BBZD_BH", "LZD_ORDE", "HZD_ORDE");
  } else {
    vscode.window.showErrorMessage(tableName + '表单不支持单元格转换');
    return;
  }

}


export async function copySQL() {
  // 获取当前工作区
  const workspaceFolders = vscode.workspace.workspaceFolders;
  if (!workspaceFolders) {
    vscode.window.showErrorMessage('请先打开一个工作区');
    return;
  }

  const editor = vscode.window.activeTextEditor;
  if (!editor) {
    vscode.window.showErrorMessage('No active editor found!');
    return;
  }


  if (editor.document.languageId !== 'markdown') {
    vscode.window.showErrorMessage('当前文档不是Markdown文件');
    return;
  }

  const lineCount = editor.document.lineCount;
  const position = editor.selection.active;
  const currentLineNumber = position.line;

  let startLine = 0;
  let endLine = lineCount;
  const lines: { lineNumber: number, lineContent: string }[] = [];
  for (let lineNumber = currentLineNumber; lineNumber >= 0; lineNumber--) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    lines.push({ lineNumber, lineContent });
    if (lineContent.startsWith("#### ")) {
      startLine = lineNumber;
      break;
    }
  }
  for (let lineNumber = currentLineNumber + 1; lineNumber < lineCount; lineNumber++) {
    const lineContent = editor.document.lineAt(lineNumber).text;
    if (lineContent.startsWith("#### ") || lineContent.startsWith("### ") || lineContent.startsWith("## ") || lineContent.startsWith("# ")) {
      endLine = lineNumber - 1;
      break;
    }
    lines.push({ lineNumber, lineContent });
  }

  lines.sort((a, b) => a.lineNumber - b.lineNumber);
  const content = lines.map(l => l.lineContent).join("\n");

  const sqlRegex = /```sql\s*([\s\S]*?)\s*```/;
  const match = content.match(sqlRegex);
  if (!match) {
    vscode.window.showErrorMessage('未找到sql');
    return;
  }

  const sql = match[1].trim();
  await vscode.env.clipboard.writeText(sql);
  vscode.window.showInformationMessage(`${sql} copied to clipboard!`);
}


// 替换函数
export function replaceSelectedText() {
  const editor = vscode.window.activeTextEditor;
  if (!editor) {
    vscode.window.showErrorMessage('No active editor found!');
    return;
  }

  const selection = editor.selection;
  const selectedText = editor.document.getText(selection);

  if (!selectedText) {
    vscode.window.showErrorMessage('No text selected!');
    return;
  }

  if (!/^[a-zA-Z]+$/.test(selectedText)) {
    vscode.window.showErrorMessage('Need select English letter!');
    return;
  }

  editor.edit(editBuilder => {
    const replacement = ExcelUtils.excelColumnToNumberStr(selectedText);
    editBuilder.replace(selection, replacement);
    vscode.window.showInformationMessage('success!');
  });
}

